package view;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TopComplaintReportDAO {
	
	private DBConnect con = null;
    private long lnoexist;
        
    public TopComplaintReportDAO (DBConnect incon)
    {
        con = incon;
        try
        {
            con.getConnection().setAutoCommit(false);
        }
        catch (SQLException e)
        {
        
        }
    }
    
    public String ViewTopComplaintReport()
    {
       StringBuffer topComplaintReport = new StringBuffer(1000);
        PreparedStatement ps= null;
        ResultSet rs=null;
        int intCustomerID =0;
        String strFirstName="";
        String strLastName="";
        int intComplaintCount=0;
        
        try
        {
          //get top 5 customers via query
          ps=con.getConnection().prepareStatement(""
          + "WITH TOP_COMPLAINERS AS "
          + "(SELECT PERSON_ID, FIRST_NAME, LAST_NAME, COUNT(PERSON_ID) AS COMPLAINTS "
          + "FROM "+con.getSchema()+".FACTORY_ORDER FO "
          + "INNER JOIN "+con.getSchema()+".PERSON P ON FO.CUSTOMER_ID = P.PERSON_ID "
          + "INNER JOIN "+con.getSchema()+".CUST_COMPLAINT CC ON FO.ORDER_ID = CC.ORDER_ID "
          + "GROUP BY PERSON_ID, FIRST_NAME, LAST_NAME "
          + "ORDER BY COMPLAINTS DESC) "
          + "SELECT PERSON_ID, FIRST_NAME, LAST_NAME, COMPLAINTS "
          + "FROM TOP_COMPLAINERS "
          + "WHERE ROWNUM <= 5");
          
            //start timer to time how long query runs within java runtime
          	final long startTime = System.nanoTime();
          	rs=ps.executeQuery();
          	//for each returned row, grab and display value
            while(rs.next())
            {
             
            	intCustomerID = rs.getInt(1);
            	strFirstName = rs.getString(2);
            	strLastName = rs.getString(3);
            	intComplaintCount = rs.getInt(4);
            	topComplaintReport.append( intCustomerID + " " + strFirstName + " " + strLastName + " " + intComplaintCount + "\n");      
            }
            //get current time, subtract start time, convert to seconds, display
            final long duration = System.nanoTime() - startTime;
            topComplaintReport.append("Display Complete. Elapsed: " + (duration/ 1000000000.0) + " seconds \n");
          rs.close();
          ps.close();
        }
        catch(SQLException e)
        {
        	topComplaintReport.append("SQL Error in get Top 5 Customer Complaint Count Report" + e.getSQLState());
        	topComplaintReport.append("\nError Code: " + e.getErrorCode());
        	topComplaintReport.append("\nMessage: " + e.getMessage());
            System.exit( 1 );
        }
        catch(Exception e)
        {
        	topComplaintReport.append("unknown Error in get Top 5 Customer Complaint Count Report");
            topComplaintReport.append("\nMessage: " + e.getMessage());
            System.exit( 1 );
        }
       return topComplaintReport.toString();
    }
}
